Link Server: SQL to SQL

Create Login on Remote Server that is the server for which we have to create the Link Server, in this case 192.168.8.31,1434 is my RemoteServer and  "TestLogin"  Login is created where its server role is public, User mapping  to Database "Testing" and assigned DataReader Role to it.

Now follow these step to create linked server on Local Server  i.e 192.168.8.31,1433 using any sysadmin Login.

In security tab select "Not be made" option and specify the Local account from drop-down list also specify the remote login which we have created in earlier step i.e remote Login Account "TestLogin"

Click on Ok now Link Server is created for "sa" user only. No user other than sa can use link server.

Let's try,

Login to your server with sa account where you have created Linked Server and under "Server Object" right click on Linked Server which was created, then click on "Test Connection

As you can see Test connection succeeded.

Quering using openrowset query:

CRUD operations:

By using the OPENQUERY function we can execute the specified pass-through queries on the specified Linked Server and return the output.

Sample Select Syntax

SELECT * FROM OPENQUERY(LinkServerName, 'select * from dbo.Products')  

Call a function or Stored Procedure

SELECT * FROM OPENQUERY(LinkServerName, 'EXEC [dbo].[CustOrdersOrders] Anurag')  

Insert records

insert OPENQUERY(LinkServerName, 'select ProductName, Description from dbo.Products')   

select 'Testing', 'Testing'  

To insert records we need to first select those columns to which records are to be inserted and pass the values through the select statement. Update records

update OPENQUERY(LinkServerName, 'select ProductName from dbo.Products where ProductID=10')   
set ProductName = 'New Test'  

To update records we need to first select those columns to which records are to be updated and pass the values through the select statement with column names. We can have multiple column names separated with a comma. Delete records

delete OPENQUERY(LinkServerName, 'select * from dbo.Products where ProductID in (9, 10, 11)')   

We just need to select those records to be deleted and execute the delete statement.

Restrict the access for Linked Server To Users:

Create Login on Remote Server that is the server for which we have to create the Link Server, in this case 192.168.8.31,1434 is my RemoteServer and  "TestLogin"  Login is created where its server role is public, User mapping  to Database "Testing" and assigned DataReader Role to it.

Now Login to your server with any other account excluding "sa" account where you have created Linked Server and under "Server Object" right click on Linked Server which was created, then click on "Test Connection".

As you can see we are unable to test connection as well as unable to query link server.

Now Login to your server with any other account which does not have "sysadmin" rights means normal user.

Here I have login to server using SQL authentication i.e "dd"  which is public user.

Check linked server under "Server Object". You will find that link server is not visible to normal user as we have specified only "sa" account during creation of linked server.

If you specify "dd" user in linked Server then link server will be visible and available to query.